package com.ruoyi.common.utils;

import com.fasterxml.jackson.annotation.JsonInclude;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import java.util.logging.Logger;

public class ExcelToJsonConverter {
    private static final Logger logger = Logger.getLogger(ExcelToJsonConverter.class.getName());
    public static void main(String[] args) {


        String excelFilePath = "C:\\Users\\lenovo\\Desktop\\化工样例\\化工.xlsx";
        String imageOutputDir = "C:\\Users\\lenovo\\Desktop\\化工样例\\images\\";
        String jsonOutputPath = "C:\\Users\\lenovo\\Desktop\\化工样例\\output.json";

        try {
            logger.info("开始处理Excel文件: " + excelFilePath);
            List<Chemical> chemicals = readExcel(excelFilePath, imageOutputDir);
            saveAsJson(chemicals, jsonOutputPath);
            logger.info("处理完成！生成记录数: " + chemicals.size());
        } catch (Exception e) {
            logger.severe("处理失败: " + e.getMessage());
            e.printStackTrace();
        }
    }


    /**
     * 读取Excel文件核心逻辑
     */
    private static List<Chemical> readExcel(String filePath, String imageDir) throws Exception {
        List<Chemical> chemicals = new ArrayList<>();
        logger.info("初始化Excel工作簿...");

        try (FileInputStream excelFile = new FileInputStream(new File(filePath));
             Workbook workbook = new XSSFWorkbook(excelFile)) {

            Sheet sheet = workbook.getSheetAt(0);
            int totalRows = sheet.getLastRowNum();
            logger.info("共发现数据行: " + (totalRows - 1));  // 减去标题行

            for (int rowNum = 1; rowNum <= totalRows; rowNum++) {  // 从第2行开始
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    logger.warning("第" + rowNum + "行为空，跳过");
                    continue;
                }

                // 物化性质过滤检查
                Cell propertiesCell = row.getCell(4);
                String properties = getCellValue(propertiesCell);
                if (properties.isEmpty()) {
                    logger.info("第" + rowNum + "行物化性质为空，已过滤");
                    continue;
                }

                // 构建化学对象
                Chemical chem = new Chemical();
                chem.setCasRegistryNumber(sanitizeCasrn(getCellValue(row.getCell(2))));
                chem.setChineseName(getCellValue(row.getCell(0)));
                chem.setEnglishName(getCellValue(row.getCell(1)));
                chem.setMolecularFormula(getCellValue(row.getCell(3)));
                chem.setChemicalProperties(properties.replace("\n", "\\n"));

                // 图片处理
                String imageUrl = getCellValue(row.getCell(5));
                if (!imageUrl.isEmpty()) {
                    String imagePath = downloadImageWithRetry(imageUrl, imageDir, chem.getCasRegistryNumber());
                    chem.setMolecularStructuralFormula(imagePath);
                }

                chemicals.add(chem);
                logger.info("成功处理第" + rowNum + "行: " + chem.getCasRegistryNumber());
            }
        }
        return chemicals;
    }


    /**
     * 下载图片并保存为PNG格式
     * 改进点：
     * - 自动处理所有图片格式
     * - 保持透明背景
     * - 三级重试机制
     */
    private static String downloadImageWithRetry(String imageUrl, String outputDir, String casNumber) {
        int maxRetries = 3;
        for (int i = 0; i < maxRetries; i++) {
            try {
                logger.info("尝试下载(" + (i+1) + "/3): " + casNumber);
                return downloadImage(imageUrl, outputDir, casNumber);
            } catch (Exception e) {
                logger.warning("下载失败: " + imageUrl + " 原因: " + e.getMessage());
                if (i == maxRetries - 1) return "";
            }
        }
        return "";
    }

    private static String downloadImage(String imageUrl, String outputDir, String casNumber) throws Exception {
        URL url = new URL(imageUrl);
        HttpURLConnection conn = (HttpURLConnection) url.openConnection();
        conn.setConnectTimeout(10000);
        conn.setReadTimeout(10000);
        conn.setRequestProperty("User-Agent", "Mozilla/5.0");

        try (InputStream stream = conn.getInputStream()) {
            BufferedImage image = ImageIO.read(stream);
            if (image == null) {
                throw new IOException("无法解码图像数据");
            }

            // 创建兼容PNG格式的图像
            BufferedImage pngImage = new BufferedImage(
                    image.getWidth(),
                    image.getHeight(),
                    BufferedImage.TYPE_INT_ARGB
            );

            // 保留透明背景
            Graphics2D g = pngImage.createGraphics();
            g.drawImage(image, 0, 0, null);
            g.dispose();

            File dir = new File(outputDir);
            if (!dir.exists() && !dir.mkdirs()) {
                throw new IOException("无法创建目录: " + outputDir);
            }

            String fileName = casNumber + ".png";
            File outputFile = new File(dir, fileName);

            // 保存为PNG格式
            if (ImageIO.write(pngImage, "png", outputFile)) {
                logger.info("图片保存成功: " + outputFile.getAbsolutePath());
                return "./images/" + fileName;
            } else {
                throw new IOException("不支持的图像格式");
            }
        } finally {
            conn.disconnect();
        }
    }

    /**
     * CASRN清洗处理
     */
    private static String sanitizeCasrn(String casrn) {
        if (casrn == null || casrn.isEmpty()) {
            String randomId = "invalid_" + UUID.randomUUID().toString().substring(0, 8);
            logger.warning("发现无效CASRN，生成随机ID: " + randomId);
            return randomId;
        }
        return casrn.split(";")[0]
                .replaceAll("[^a-zA-Z0-9-]", "_")
                .replaceAll("_+", "_");
    }

    /**
     * 单元格值获取工具
     */
    private static String getCellValue(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING: return cell.getStringCellValue().trim();
            case NUMERIC: return String.valueOf((int) cell.getNumericCellValue());
            default: return "";
        }
    }

    /**
     * JSON导出方法
     */
    private static void saveAsJson(List<Chemical> chemicals, String outputPath) throws IOException {
        ObjectMapper mapper = new ObjectMapper();
        File outputFile = new File(outputPath);
        mapper.writerWithDefaultPrettyPrinter().writeValue(outputFile, chemicals);
        logger.info("JSON文件已生成: " + outputFile.getAbsolutePath());
    }




    @JsonInclude(JsonInclude.Include.NON_EMPTY) // 空字段不导出
    static class Chemical {
        private String cas_registry_number;
        private String chinese_name;
        private String english_name;
        private String molecular_structural_formula;
        private String molecular_formula;
        private String chemical_properties;

        // Getters and Setters
        public String getCasRegistryNumber() { return cas_registry_number; }
        public void setCasRegistryNumber(String value) { this.cas_registry_number = value; }
        public String getChineseName() { return chinese_name; }
        public void setChineseName(String value) { this.chinese_name = value; }
        public String getEnglishName() { return english_name; }
        public void setEnglishName(String value) { this.english_name = value; }
        public String getMolecularStructuralFormula() { return molecular_structural_formula; }
        public void setMolecularStructuralFormula(String value) { this.molecular_structural_formula = value; }
        public String getMolecularFormula() { return molecular_formula; }
        public void setMolecularFormula(String value) { this.molecular_formula = value; }
        public String getChemicalProperties() { return chemical_properties; }
        public void setChemicalProperties(String value) { this.chemical_properties = value; }
    }
}
